package com.meizu.dao;

import com.meizu.entity.*;
import com.meizu.utils.RowMapper;
import com.meizu.utils.SQLHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 商品类的Dao
 * @author hxf
 * @date 2023/3/23
 * gstate=1 上架，gstate=-1 下架
 **/
public class GoodsDao {
    SQLHelper helper = new SQLHelper ( );

    /**
     * 根据详情id 查询 商品名字，优惠，折扣
     * @param did 商品详情ID
     * @return 商品对象
     */
    public Goods selectConDetailById(Integer did) {
        String sql = "select g.* from tb_goods g,tb_detail d where g.gid=d.gid and d.did=?";
        return helper.one (sql, new GoodsRowMapper ( ), did);
    }

    /**
     * 根据商品id（gid）查询商品信息
     * @param gid 商品id
     * @return 商品对象
     */
    public Goods selectGoodsById(Integer gid) {
        String sql = "select * from tb_goods where gid=?";
        return helper.one (sql, new GoodsRowMapper ( ), gid);
    }

    /**
     * 查询所有商品信息
     * @return
     */
    public List<Goods> selectGoods() {
        String sql = "select  * from tb_goods where ";
        return helper.query (sql, new GoodsRowMapper ( ));
    }

    //内部类
    class GoodsRowMapper implements RowMapper<Goods> {

        @Override
        public Goods map(ResultSet rs) throws SQLException {
            return new Goods (rs.getInt ("gid"),
                    rs.getInt ("sid"),
                    rs.getString ("gname"),
                    rs.getString ("gdetail"),
                    rs.getString ("gdiscount"),
                    rs.getString ("gphoto"),
                    rs.getString ("gstate"));
        }
    }

    //gname,gdetail,gdiscount
    class GoodsNDDRowMapper implements RowMapper<Goods> {

        @Override
        public Goods map(ResultSet rs) throws SQLException {
            Goods goods = new Goods ( );
            goods.setGname (rs.getString ("gname"));
            goods.setGname (rs.getString ("gdetail"));
            goods.setGname (rs.getString ("gdiscount"));
            return goods;
        }
    }
    /**
     * 根据类别编号查询 【上架】 商品
     * @param sid
     * @return
     */
    public List<Goods> selectGoodsDetailQT(Integer sid) {
        String sql = "select a.*,b.did,b.dprice from tb_goods a left join tb_detail b on a.gid = b.gid\n" +
                "where a.sid=? and a.gstate=1 group by a.gname order by a.gid";
        return helper.query (sql, new GoodsDetailRowMapper ( ), sid);
    }
    /**
     * 根据类别编号查询商品（给后台）
     * @param sid
     * @return
     */
    public List<Goods> selectGoodsDetail(Integer sid) {
        String sql = "select a.*,b.did,b.dprice from tb_goods a left join tb_detail b on a.gid = b.gid\n" +
                "where a.sid=? group by a.gname order by a.gid desc";
        return helper.query(sql, new GoodsDetailRowMapper ( ), sid);
    }

    /**
     * 根据商品类别id模糊查询商品（给后台）
     * @param str
     * @return
     */
    public List<Goods> selectGoodMF(String str){
        String sql = "select a.*,b.did,b.dprice from tb_goods a left join tb_detail b on a.gid = b.gid\n" +
                "where a.gname like concat('%',?,'%') group by a.gname";
        return helper.query(sql,new GoodsDetailRowMapper() ,str);
    }


    /**
     * 查询商品类别（名称）[给后台]
     * @return
     */
    public List<GoodsSort> selectGoodsSort(){
        String sql="select * from tb_goodssort order by sid desc";
        return helper.query (sql,new GoodsSortRowMapper ());
    }

    /**
     * 查询商品类别（名称+图片）
     * @return
     */
    public List<GoodsSort> selectGoodsSortAsc(){
        String sql="select * from tb_goodssort";
        return helper.query (sql,new GoodsSortRowMapper ());
    }
    /**
     * 查询所用商品种类的数量（给后台）
     * @Author hxf
     * @Date 2023/4/11 9:55
     **/
    public Goods countGoods(){
        String sql = "select count(*) from tb_goods";
        return helper.one(sql, new RowMapper<Goods>() {
            @Override
            public Goods map(ResultSet rs) throws SQLException {
                return new Goods(rs.getInt(1));
            }
        });
    }
    /**
     * 新增商品类别（给后台）
     * @Author lyz
     * @Date 2023/4/12 14:01
     * @Param []
     * @return java.lang.Integer
    **/
    public Integer addGoodSort(String sname){
        String sql="insert into tb_goodssort values(null,?,null)";
        return helper.insert (sql,sname);
    }
    /**
     * 修改商品状态（给后台）
     * @Author lyz
     * @Date 2023/4/13 9:04
     * @Param [gstate, gid]
     * @return java.lang.Integer
    **/
    public Integer updateGstate(Integer gstate,Integer gid){
        String sql="update tb_goods set gstate=? where gid=?";
        return helper.update (sql,gstate,gid);

    }
    /**
     * 查询商品详情（给后台）
     * @Author lyz
     * @Date 2023/4/13 14:03
     * @Param [gid]
     * @return java.util.List<com.meizu.entity.Detail>
    **/
    public List<Detail> selectGoodDetail(Integer gid){
        String sql="select a.*,b.gname from tb_detail a left join " +
                "tb_goods b on a.gid=b.gid  where a.gid=?";
       return helper.query (sql,new GoodDetailRowMapper(),gid);
    }
    /**
     * 查询商品详情图片（给后台）
     * @Author lyz
     * @Date 2023/4/13 15:06
     * @Param [gid]
     * @return java.util.List<com.meizu.entity.Photo>
    **/
    public List<Photo> selectPhoto(Integer gid){
        String sql="select c.photo from tb_goods a right join tb_detail b on a.gid = b.gid " +
                "right join tb_photo c on b.did = c.did where a.gid=? group by b.dcolor";
        return helper.query (sql, new RowMapper<Photo> ( ) {
            @Override
            public Photo map(ResultSet rs) throws SQLException {
                return new Photo (rs.getString (1));
            }
        },gid);
    }
    /**
     * 查商品版本的集合（给后台）
     * @Author lyz
     * @Date 2023/4/14 8:27
     * @Param []
     * @return java.util.List<com.meizu.entity.Detail>
    **/

    public List<Detail> selectDversion(){
      String sql="select dversion from tb_detail group by dversion";
      return helper.query (sql, new RowMapper<Detail> ( ) {
          @Override
          public Detail map(ResultSet rs) throws SQLException {
              return new Detail (rs.getString (1));
          }
      });
    }
    /**
     * 查询商品的颜色集合（给后台）
     * @Author lyz
     * @Date 2023/4/14 8:38
     * @Param []
     * @return java.util.List<com.meizu.entity.Detail>
    **/
    public List<Detail> selectDcolor(){
        String sql="select dcolor from tb_detail group by dcolor";
        return helper.query (sql, new RowMapper<Detail> ( ) {
            @Override
            public Detail map(ResultSet rs) throws SQLException {
                Detail detail=new Detail ();
                detail.setDcolor (rs.getString (1));
                return detail;
            }
        });
    }
    /**
     * 后台新增商品
     * @Author lyz
     * @Date 2023/4/14 20:02
     * @Param [goods]
     * @return java.lang.Integer
    **/

    public Integer insertGoods(Goods goods){
        String sql="insert into tb_goods values(null,?,?,?,?,?,-1)";
        return helper.insert (sql,goods.getSid (),goods.getGname (),
        goods.getGdetail (),goods.getGdiscount (),goods.getGphoto ());
    }
    /**
     * 后台根据商品id查商品,修改用
     * @Author lyz
     * @Date 2023/4/14 20:20
     * @Param [gid]
     * @return com.meizu.entity.Goods
    **/
    public Goods selectGoodsOne(Integer gid){
        String sql="select * from tb_goods where gid=?";
        return helper.one (sql,new GoodsRowMapper(),gid);
    }

    /**
     * 后台根据商品id查商品,修改商品信息
     * @param gname
     * @param gdetail
     * @param gdiscount
     * @param gphoto
     * @param gid
     * @return
     */
    public Integer updateGoodXX(String gname,
                              String gdetail,
                              String gdiscount,
                              String gphoto,
                              Integer gid){
        String sql="update tb_goods set gname=?,gdetail=?,gdiscount=?,gphoto=? where gid=?";
        return helper.update(sql,gname,gdetail,gdiscount,gphoto,gid);
    }

    /**
     * 查询所有商品名称
     * @return
     */
    public List<Goods> selectGoodsName(Integer gid){
        String sql="select gname from tb_goods where not gid=?";
        return helper.query(sql,new GoodsNameRowMapper(),gid);
    }
    class GoodsNameRowMapper implements RowMapper<Goods>{

        @Override
        public Goods map(ResultSet rs) throws SQLException {
            return new Goods(rs.getString("gname"));
        }
    }
    //内部类
    class GoodsDetailRowMapper extends GoodsRowMapper {
        @Override
        public Goods map(ResultSet rs) throws SQLException {
            Goods goods = super.map (rs);
            goods.setDid (rs.getInt ("did"));
            goods.setDprice (rs.getBigDecimal ("dprice"));
            return goods;
        }
    }
    class GoodsSortRowMapper implements RowMapper<GoodsSort>{

        @Override
        public GoodsSort map(ResultSet rs) throws SQLException {
            return new GoodsSort (rs.getInt ("sid"),
                    rs.getString ("sname"),
                    rs.getString ("sphoto"));
        }
    }
    class GoodDetailRowMapper implements RowMapper<Detail>{
        @Override
        public Detail map(ResultSet rs) throws SQLException {
            return new Detail (rs.getInt (1),
                    rs.getInt (2),
                    rs.getInt (3),
                    rs.getString (4),
                    rs.getString (5),
                    rs.getString (6)
                    );
        }
    }
}